package com.css.fxfzypg.modules.baseData.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.*;
import com.css.fxfzypg.modules.baseData.queryParams.SeismicSetoutQueryParams;
import com.css.fxfzypg.modules.baseData.repository.SeismicSetoutRepository;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

@Repository
public class SeismicSetoutRepositoryImpl implements SeismicSetoutRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Resource
    SysRoleService sysRoleService;

    @Resource
    SUserService sUserService;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int updateSeismicSetout(ScSeismicsetout entity) {
        String sql = "update sys_sc_seismicsetout set province =:province,city =:city,prefecture =:prefecture," +
                "sc_medicalhelp =:scMedicalhelp,sc_bzxtjsqk =:scBzxtjsqk,sc_dzyjzztxhyatx =:scDzyjzztxhyatx," +
                "sc_dzyjgzjz =:scDzyjgzjz,sc_yjylkzqk =:scYjylkzqk,sc_smxyjbzqk =:scSmxyjbzqk,sc_cszhffqk =:scCszhffqk," +
                "sc_sfyyjya =:scSfyyjya,sc_sfyyawj =:scSfyyawj,sc_yjdcxq =:scYjdcxq,sc_zdmbbznl =:scZdmbbznl," +
                "sc_yyyqyjnl =:scYyyqyjnl,sc_kpxjhyl =:scKpxjhyl,sc_qtdftzyjzbcs =:scQtdftzyjzbcs," +
                "update_user =:updateUser,update_time =:updateTime,is_delete =:isDelete," +
                "sc_medicalhelp_attachment =:scMedicalhelpAttachment,sc_bzxtjsqk_attachment =:scBzxtjsqkAttachment," +
                "sc_dzyjzztxhyatx_attachment =:scDzyjzztxhyatxAttachment,sc_dzyjgzjz_attachment =:scDzyjgzjzAttachment," +
                "sc_yjylkzqk_attachment =:scYjylkzqkAttachment,sc_smxyjbzqk_attachment =:scSmxyjbzqkAttachment," +
                "sc_cszhffqk_attachment =:scCszhffqkAttachment,sc_sfyyjya_attachment =:scSfyyjyaAttachment," +
                "sc_sfyyawj_attachment = :scSfyyawjAttachment,sc_yjdcxq_attachment =:scYjdcxqAttachment," +
                "sc_zdmbbznl_attachment =:scZdmbbznlAttachment,sc_yyyqyjnl_attachment =:scYyyqyjnlAttachment," +
                "sc_kpxjhyl_attachment =:scKpxjhylAttachment,sc_qtdftzyjzbcs_attachment =:scQtdftzyjzbcsAttachment" +
                " where sc_id = :scId  and is_delete = '" + YNEnum.N + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int updateScSupplies(ScSupplies entity) {
        String sql = "update sys_sc_supplies set sc_supplies_name =:scSuppliesName,sc_supplies_brand =:scSuppliesBrand," +
                "sc_supplies_number =:scSuppliesNumber,sc_supplies_unit =:scSuppliesUnit," +
                "sc_supplies_note =:scSuppliesNote,update_user =:updateUser,update_time =:updateTime," +
                "is_delete =:isDelete" +
                " where su_id = :suId  and is_delete = '" + YNEnum.N + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int updateScRescue(ScRescue entity) {
        String sql = "update sys_sc_rescue set sc_rescue_name =:scRescueName,sc_rescue_industry =:scRescueIndustry," +
                "sc_rescue_unit =:scRescueUnit,sc_rescue_address =:scRescueAddress,sc_rescue_number =:scRescueNumber," +
                "sc_rescue_note =:scRescueNote,update_user =:updateUser,update_time =:updateTime," +
                "is_delete =:isDelete" +
                " where re_id = :reId  and is_delete = '" + YNEnum.N + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int updateScEquip(ScEquip entity) {
        String sql = "update sys_sc_equip set sc_equip_name =:scEquipName,sc_equip_number =:scEquipNumber," +
                "sc_equip_note =:scEquipNote,update_user =:updateUser,update_time =:updateTime,is_delete =:isDelete" +
                " where eq_id = :eqId  and is_delete = '" + YNEnum.N + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int updateScSanctuary(ScSanctuary entity) {
        String sql = "update sys_sc_sanctuary set sc_sanctuary_number = :scSanctuaryNumber," +
                "sc_sanctuary_area = :scSanctuaryArea,sc_sanctuary_numberpeople = :scSanctuaryNumberpeople," +
                "update_user =:updateUser,update_time =:updateTime,is_delete =:isDelete" +
                " where sa_id = :saId  and is_delete = '" + YNEnum.N + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int updateScMedical(ScMedical entity) {
        String sql = "update sys_sc_medical set sc_medical_name = :scMedicalName,sc_medical_doctor = :scMedicalDoctor," +
                "sc_medical_nurse = :scMedicalNurse,sc_medical_note = :scMedicalNote,update_user =:updateUser," +
                "update_time =:updateTime,is_delete =:isDelete" +
                " where me_id = :meId  and is_delete = '" + YNEnum.N + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    @Transactional
    public int saveSeismicSetout(ScSeismicsetout seismicSetout) {
        String sql = "insert into sys_sc_seismicsetout(sc_id,province,city,prefecture,sc_medicalhelp,sc_bzxtjsqk," +
                "sc_dzyjzztxhyatx,sc_dzyjgzjz,sc_yjylkzqk,sc_smxyjbzqk,sc_cszhffqk,sc_sfyyjya,sc_sfyyawj,sc_yjdcxq," +
                "sc_zdmbbznl,sc_yyyqyjnl,sc_kpxjhyl,sc_qtdftzyjzbcs,sc_medicalhelp_attachment,sc_bzxtjsqk_attachment," +
                "sc_dzyjzztxhyatx_attachment,sc_dzyjgzjz_attachment,sc_yjylkzqk_attachment,sc_smxyjbzqk_attachment," +
                "sc_cszhffqk_attachment,sc_sfyyjya_attachment,sc_sfyyawj_attachment,sc_yjdcxq_attachment," +
                "sc_zdmbbznl_attachment,sc_yyyqyjnl_attachment,sc_kpxjhyl_attachment,sc_qtdftzyjzbcs_attachment," +
                "is_delete,create_user,create_time,province_id,city_id,prefecture_id,province_code,city_code,prefecture_code)" +
                " values(:scId,:province,:city,:prefecture,:scMedicalhelp,:scBzxtjsqk,:scDzyjzztxhyatx,:scDzyjgzjz," +
                ":scYjylkzqk,:scSmxyjbzqk,:scCszhffqk,:scSfyyjya,:scSfyyawj,:scYjdcxq,:scZdmbbznl,:scYyyqyjnl," +
                ":scKpxjhyl,:scQtdftzyjzbcs,:scMedicalhelpAttachment,:scBzxtjsqkAttachment,:scDzyjzztxhyatxAttachment," +
                ":scDzyjgzjzAttachment,:scYjylkzqkAttachment,:scSmxyjbzqkAttachment,:scCszhffqkAttachment," +
                ":scSfyyjyaAttachment,:scSfyyawjAttachment,:scYjdcxqAttachment,:scZdmbbznlAttachment," +
                ":scYyyqyjnlAttachment,:scKpxjhylAttachment,:scQtdftzyjzbcsAttachment,:isDelete,:createUser," +
                ":createTime,:provinceId,:cityId,:prefectureId,:provinceCode,:cityCode,:prefectureCode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(seismicSetout);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveScSupplies(ScSupplies entity) {
        String sql = "insert into sys_sc_supplies(su_id,sc_supplies_name,sc_supplies_brand,sc_supplies_number," +
                "sc_supplies_unit,sc_supplies_note,is_delete,create_user,create_time,sc_id,extends1,extends2,extends3)" +
                "values(:suId,:scSuppliesName,:scSuppliesBrand,:scSuppliesNumber,:scSuppliesUnit,:scSuppliesNote," +
                ":isDelete,:createUser,:createTime,:scId,:extends1,:extends2,:extends3)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveScRescue(ScRescue entity) {
        String sql = "insert into sys_sc_rescue(re_id,sc_rescue_name,sc_rescue_industry,sc_rescue_unit,sc_rescue_address," +
                "sc_rescue_number,sc_rescue_note,is_delete,create_user,create_time,sc_id,extends1,extends2,extends3)" +
                "values(:reId,:scRescueName,:scRescueIndustry,:scRescueUnit,:scRescueAddress,:scRescueNumber," +
                ":scRescueNote,:isDelete,:createUser,:createTime,:scId,:extends1,:extends2,:extends3)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveScEquip(ScEquip entity) {
        String sql = "insert into sys_sc_equip(eq_id,sc_equip_name,sc_equip_number,sc_equip_note," +
                "is_delete,create_user,create_time,sc_id,extends1,extends2,extends3)" +
                "values(:eqId,:scEquipName,:scEquipNumber,:scEquipNote,:isDelete,:createUser,:createTime," +
                ":scId,:extends1,:extends2,:extends3)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveScSanctuary(ScSanctuary entity) {
        String sql = "insert into sys_sc_sanctuary(sa_id,sc_sanctuary_number,sc_sanctuary_area," +
                "sc_sanctuary_numberpeople,is_delete,create_user,create_time,sc_id,extends1,extends2,extends3)" +
                "values(:saId,:scSanctuaryNumber,:scSanctuaryArea,:scSanctuaryNumberpeople,:isDelete," +
                ":createUser,:createTime,:scId,:extends1,:extends2,:extends3)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveScMedical(ScMedical entity) {
        String sql = "insert into sys_sc_medical(me_id,sc_medical_name,sc_medical_doctor,sc_medical_nurse," +
                "sc_medical_note,is_delete,create_user,create_time,sc_id,extends1,extends2,extends3)" +
                "values(:meId,:scMedicalName,:scMedicalDoctor,:scMedicalNurse,:scMedicalNote," +
                ":isDelete,:createUser,:createTime,:scId,:extends1,:extends2,:extends3)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public JSONObject getAllSeismicSetoutParams(SeismicSetoutQueryParams queryParams, int curPage,
                                                int pageSize) {
        StringBuilder str = new StringBuilder("");
        StringBuilder sql = new StringBuilder(" select  s.* from sys_sc_seismicsetout s ");
        StringBuilder whereSql = new StringBuilder(" where s.is_delete = '" + YNEnum.N.toString() + "'");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and s.province_id ='" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and s.city_id  ='" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefectureId())) {
            whereSql.append(" and s.prefecture_id ='" + queryParams.getPrefectureId() + "'");
        }
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) || roles.contains(FxfzConstants.YPG_COUN_YW_ROLE)) {
            //国家级管理员和国家级业务人员可以全看全部
        } else if (roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) || roles.contains(FxfzConstants.YPG_PRI_YW_ROLE)) {
            //省级管理员和省级业务人员可以全看自己
            whereSql.append(" and s.province_id = '" + sUser.getProvinceId() + "'");
        }
//        StringBuilder orderSql = new StringBuilder(" order by s.prefecture_code");
        StringBuilder orderSql = new StringBuilder();
        if ("0".equals(queryParams.getOption())) {
            orderSql.append(" order by s.province_code,s.city_code,s.prefecture_code");
        }
        if ("1".equals(queryParams.getOption())) {
            orderSql.append(" order by COALESCE (s.update_time,s.create_time) desc");
        }
        sql.append(whereSql).append(orderSql).toString();
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ScSeismicsetout.class)).size();
        List<ScSeismicsetout> gdpEntities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ScSeismicsetout.class));
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("rows", gdpEntities);
        jsonObject.put("total", total);
        return jsonObject;
    }

    @Override
    public ScSeismicsetout getSeismicSetoutById(String id) {
        String sql = "select * from sys_sc_seismicsetout where sc_id = '" + id + "' and is_delete = '" + YNEnum.N + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ScSeismicsetout.class));
    }

    @Override
    public ScSupplies getScSuppliesById(String id) {
        String sql = "select * from sys_sc_supplies where su_id = '" + id + "' and is_delete = '" + YNEnum.N + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ScSupplies.class));
    }

    @Override
    public ScRescue getScRescueById(String id) {
        String sql = "select * from sys_sc_rescue where re_id = '" + id + "' and is_delete = '" + YNEnum.N + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ScRescue.class));
    }

    @Override
    public ScEquip getScEquipById(String id) {
        String sql = "select * from sys_sc_equip where eq_id = '" + id + "' and is_delete = '" + YNEnum.N + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ScEquip.class));
    }

    @Override
    public ScSanctuary getScSanctuaryById(String id) {
        String sql = "select * from sys_sc_sanctuary where sa_id = '" + id + "' and is_delete = '" + YNEnum.N + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ScSanctuary.class));
    }

    @Override
    public ScMedical getScMedicalById(String id) {
        String sql = "select * from sys_sc_medical where me_id = '" + id + "' and is_delete = '" + YNEnum.N + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ScMedical.class));
    }

    @Override
    public JSONObject getDetailsById(String id, String type, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * ");
        if ("1".equals(type)) {
            sql.append(" from sys_sc_supplies");
        } else if ("2".equals(type)) {
            sql.append(" from sys_sc_rescue");
        } else if ("3".equals(type)) {
            sql.append(" from sys_sc_equip");
        } else if ("4".equals(type)) {
            sql.append(" from sys_sc_sanctuary");
        } else if ("5".equals(type)) {
            sql.append(" from sys_sc_medical");
        }
        sql.append(" where sc_id = '" + id + "' and is_delete = '" + YNEnum.N + "' order by create_time desc");

        // String sql="select *  from sys_sc_supplies where sc_id = '"+id+"' and is_delete = '"+YNEnum.N+"' order by create_time desc";
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        JSONObject jsonObject = new JSONObject();
        if ("1".equals(type)) {
            jsonObject.put("total", jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ScSupplies.class)).size());
            jsonObject.put("rows", jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ScSupplies.class)));
        } else if ("2".equals(type)) {
            jsonObject.put("total", jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ScRescue.class)).size());
            jsonObject.put("rows", jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ScRescue.class)));
        } else if ("3".equals(type)) {
            jsonObject.put("total", jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ScEquip.class)).size());
            jsonObject.put("rows", jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ScEquip.class)));
        } else if ("4".equals(type)) {
            jsonObject.put("total", jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ScSanctuary.class)).size());
            jsonObject.put("rows", jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ScSanctuary.class)));
        } else if ("5".equals(type)) {
            jsonObject.put("total", jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ScMedical.class)).size());
            jsonObject.put("rows", jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ScMedical.class)));
        }
        return jsonObject;
    }

    @Override
    public ScSeismicsetout getSeismicSetoutBydivision(String provinceId, String cityId, String prefectureId) {
        String sql = "select * from sys_sc_seismicsetout where province_id ='" + provinceId + "' and city_id ='" + cityId + "'" +
                " and prefecture_id ='" + prefectureId + "' and is_delete='" + YNEnum.N.toString() + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ScSeismicsetout.class));
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteSeismicSetoutsById(String id, String type, Timestamp timestamp, String userId) {
        StringBuilder sql = new StringBuilder();
        switch (type) {
            case "0":
                sql.append("update sys_sc_seismicsetout ");
                break;
            case "1":
                sql.append("update sys_sc_supplies ");
                break;
            case "2":
                sql.append("update sys_sc_rescue ");
                break;
            case "3":
                sql.append("update sys_sc_equip ");
                break;
            case "4":
                sql.append("update sys_sc_sanctuary ");
                break;
            case "5":
                sql.append("update sys_sc_medical ");
                break;
        }
        sql.append(" set is_delete = '" + YNEnum.Y + "', update_time = '" + timestamp + "' , " +
                "update_user = '" + userId + "' where sc_id = '" + id + "' and is_delete='" + YNEnum.N.toString() + "'");
        jdbcTemplate.update(sql.toString());
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void delDetails(String id, String type, Timestamp timestamp, String userId) {
        StringBuilder sql = new StringBuilder();
        switch (type) {
            case "1":
                sql.append("update sys_sc_supplies ");
                break;
            case "2":
                sql.append("update sys_sc_rescue ");
                break;
            case "3":
                sql.append("update sys_sc_equip ");
                break;
            case "4":
                sql.append("update sys_sc_sanctuary ");
                break;
            case "5":
                sql.append("update sys_sc_medical ");
                break;
        }
        sql.append(" set is_delete = '" + YNEnum.Y + "', update_time = '" + timestamp + "' , " +
                "update_user = '" + userId + "'");
        switch (type) {
            case "1":
                sql.append(" where su_id = '" + id + "' and is_delete='" + YNEnum.N.toString() + "'");
                break;
            case "2":
                sql.append(" where re_id = '" + id + "' and is_delete='" + YNEnum.N.toString() + "'");
                break;
            case "3":
                sql.append(" where eq_id = '" + id + "' and is_delete='" + YNEnum.N.toString() + "'");
                break;
            case "4":
                sql.append(" where sa_id = '" + id + "' and is_delete='" + YNEnum.N.toString() + "'");
                break;
            case "5":
                sql.append(" where me_id = '" + id + "' and is_delete='" + YNEnum.N.toString() + "'");
                break;
        }
        jdbcTemplate.update(sql.toString());
    }

    @Override
    public List<SeismicSetout> getAll() {
        return jdbcTemplate.query("select * from sys_sc_seismicsetout where is_delete = '" + YNEnum.N + "'",
                new BeanPropertyRowMapper<>(SeismicSetout.class));
    }

    @Override
    public void importExcel(List<SeismicSetout> seismicSetouts) {
        jdbcTemplate.batchUpdate("insert into sys_sc_seismicsetout (sc_id,sc_province,sc_city ,sc_counties," +
                        "sc_supplies_name," +
                        " sc_supplies_brand ,sc_supplies_number ," +
                        "sc_supplies_unit,sc_supplies_note ," +
                        "sc_rescue_name ,sc_rescue_industry ," +
                        "sc_rescue_unit ,sc_rescue_address ," +
                        "sc_rescue_number ,sc_rescue_note ," +
                        "sc_equip_name ,sc_equip_number," +
                        "sc_equip_note ,sc_sanctuary_number," +
                        "sc_sanctuary_area ,sc_sanctuary_numberpeople," +
                        "sc_medical_name ,sc_medical_doctor ," +
                        "sc_medical_nurse ,sc_medical_note ," +
                        "sc_medical_medicalhelp ,sc_medical_bzxtjsqk ," +
                        "sc_medical_dzyjzztxhyatx,sc_medical_dzyjgzjz ," +
                        "sc_medical_yjylkzqk ,sc_medical_smxyjbzqk ," +
                        "sc_medical_cszhffqk ,sc_medical_sfyyjya," +
                        "sc_medical_sfyyawj ,sc_medical_yjdcxq," +
                        "sc_medical_zdmbbznl,sc_medical_yyyqyjnl ," +
                        "sc_medical_kpxjhyl ,sc_medical_qtdftzyjzbcs," +
                        "sc_createtime ,is_delete )values(?,?,?,?,?,?,?,?,?,?," +
                        "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                new BatchPreparedStatementSetter() {
                    @Override
                    public int getBatchSize() {
                        return seismicSetouts.size();
                    }

                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setString(1, seismicSetouts.get(i).getScId());
                        ps.setString(2, seismicSetouts.get(i).getProvince());
                        ps.setString(3, seismicSetouts.get(i).getCity());
                        ps.setString(4, seismicSetouts.get(i).getPrefecture());
                        ps.setString(5, seismicSetouts.get(i).getScSuppliesName());
                        ps.setString(6, seismicSetouts.get(i).getScSuppliesBrand());
                        ps.setString(7, seismicSetouts.get(i).getScSuppliesNumber());
                        ps.setString(8, seismicSetouts.get(i).getScSuppliesUnit());
                        ps.setString(9, seismicSetouts.get(i).getScSuppliesNote());
                        ps.setString(10, seismicSetouts.get(i).getScRescueName());
                        ps.setString(11, seismicSetouts.get(i).getScRescueIndustry());
                        ps.setString(12, seismicSetouts.get(i).getScRescueUnit());
                        ps.setString(13, seismicSetouts.get(i).getScRescueAddress());
                        ps.setString(14, seismicSetouts.get(i).getScRescueNumber());
                        ps.setString(15, seismicSetouts.get(i).getScRescueNote());
                        ps.setString(16, seismicSetouts.get(i).getScEquipName());
                        ps.setInt(17, seismicSetouts.get(i).getScEquipNumber());
                        ps.setString(18, seismicSetouts.get(i).getScEquipNote());
                        ps.setInt(19, seismicSetouts.get(i).getScSanctuaryNumber());
                        ps.setBigDecimal(20, seismicSetouts.get(i).getScSanctuaryArea());
                        ps.setInt(21, seismicSetouts.get(i).getScSanctuaryNumberpeople());
                        ps.setString(22, seismicSetouts.get(i).getScMedicalName());
                        ps.setInt(23, seismicSetouts.get(i).getScMedicalDoctor());
                        ps.setInt(24, seismicSetouts.get(i).getScMedicalNurse());
                        ps.setString(25, seismicSetouts.get(i).getScMedicalNote());
                        ps.setString(26, seismicSetouts.get(i).getScMedicalMedicalhelp());
                        ps.setString(27, seismicSetouts.get(i).getScMedicalBzxtjsqk());
                        ps.setString(28, seismicSetouts.get(i).getScMedicalDzyjzztxhyatx());
                        ps.setString(29, seismicSetouts.get(i).getScMedicalDzyjgzjz());
                        ps.setString(30, seismicSetouts.get(i).getScMedicalYjylkzqk());
                        ps.setString(31, seismicSetouts.get(i).getScMedicalSmxyjbzqk());
                        ps.setString(32, seismicSetouts.get(i).getScMedicalCszhffqk());
                        ps.setString(33, seismicSetouts.get(i).getScMedicalSfyyjya());
                        ps.setString(34, seismicSetouts.get(i).getScMedicalSfyyawj());
                        ps.setString(35, seismicSetouts.get(i).getScMedicalYjdcxq());
                        ps.setString(36, seismicSetouts.get(i).getScMedicalZdmbbznl());
                        ps.setString(37, seismicSetouts.get(i).getScMedicalYyyqyjnl());
                        ps.setString(38, seismicSetouts.get(i).getScMedicalKpxjhyl());
                        ps.setString(39, seismicSetouts.get(i).getScMedicalQtdftzyjzbcs());
                        java.sql.Date createTime = new java.sql.Date(seismicSetouts.get(i).getCreateTime().getTime());
                        ps.setDate(40, createTime);
                        ps.setString(41, seismicSetouts.get(i).getIsDelete());

                    }
                });
    }

    @Override
    public List<ScSeismicsetout> getAllData(String province, String city, String prefecture) {
        String sql = "select * from sys_sc_seismicsetout where province='" + province + "' and city='" + city + "' and prefecture='" + prefecture + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ScSeismicsetout.class));
    }

    @Override
    public List<ScSupplies> getWz(String scId) {
        String sql = "select * from sys_sc_supplies where sc_id='" + scId + "' and is_delete='0'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ScSupplies.class));
    }

    @Override
    public List<ScRescue> getScRescue(String scId) {
        String sql = "select * from sys_sc_rescue where sc_id='" + scId + "' and is_delete='0'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ScRescue.class));
    }

    @Override
    public List<ScEquip> getEquip(String scId) {
        String sql = "select * from sys_sc_equip where sc_id='" + scId + "' and is_delete='0'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ScEquip.class));
    }

    @Override
    public List<ScSanctuary> getSc(String scId) {
        String sql = "select * from sys_sc_sanctuary where sc_id='" + scId + "' and is_delete='0'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ScSanctuary.class));
    }

    @Override
    public List<ScMedical> getMe(String scId) {
        String sql = "select * from sys_sc_medical where sc_id='" + scId + "' and is_delete='0'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ScMedical.class));
    }

    @Override
    public ScSeismicsetout findByAreaId(String areaId) {
        String sql = "select * from sys_sc_seismicsetout " +
                "where prefecture_id ='" + areaId + "' and is_delete='" + YNEnum.N.toString() + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ScSeismicsetout.class));
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {

        /*
        * type 类型：1,ScSupplies:应急物资储备;
         *             2,ScRescue:应急救援力量;
         *             3,ScEquip:应急救援装备;
         *             4,ScSanctuary:避难场所;
         *             5,ScMedical:医疗力量
        * */
        if (jdbcTemplate.queryForObject("SELECT COUNT\n" +
                "\t( * ) \n" +
                "FROM\n" +
                "\t\"sys_sc_seismicsetout\" sc\n" +
                "\tLEFT JOIN sys_sc_equip se ON sc.sc_id = se.sc_id \n" +
                "WHERE\n" +
                "\tsc.province = '"+province+"' \n" +
                "\tAND sc.city = '"+city+"' \n" +
                "\tAND sc.prefecture = '"+county+"' \n" +
                "\tAND se.is_delete = '0' \n" +
                "\tAND sc.is_delete = '0'", Integer.class)>0&&
                jdbcTemplate.queryForObject("SELECT COUNT\n" +
                "\t( * ) \n" +
                "FROM\n" +
                "\t\"sys_sc_seismicsetout\" sc\n" +
                "\tLEFT JOIN sys_sc_medical se ON sc.sc_id = se.sc_id \n" +
                "WHERE\n" +
                "\tsc.province = '"+province+"' \n" +
                "\tAND sc.city = '"+city+"' \n" +
                "\tAND sc.prefecture = '"+county+"' \n" +
                "\tAND se.is_delete = '0' \n" +
                "\tAND sc.is_delete = '0'",Integer.class)>0&&
                jdbcTemplate.queryForObject("SELECT COUNT\n" +
                "\t( * ) \n" +
                "FROM\n" +
                "\t\"sys_sc_seismicsetout\" sc\n" +
                "\tLEFT JOIN sys_sc_rescue se ON sc.sc_id = se.sc_id \n" +
                "WHERE\n" +
                "\tsc.province = '"+province+"' \n" +
                "\tAND sc.city = '"+city+"' \n" +
                "\tAND sc.prefecture = '"+county+"' \n" +
                "\tAND se.is_delete = '0' \n" +
                "\tAND sc.is_delete = '0'",Integer.class)>0
                &&jdbcTemplate.queryForObject("SELECT COUNT\n" +
                "\t( * ) \n" +
                "FROM\n" +
                "\t\"sys_sc_seismicsetout\" sc\n" +
                "\tLEFT JOIN sys_sc_sanctuary se ON sc.sc_id = se.sc_id \n" +
                "WHERE\n" +
                "\tsc.province = '"+province+"' \n" +
                "\tAND sc.city = '"+city+"' \n" +
                "\tAND sc.prefecture = '"+county+"' \n" +
                "\tAND se.is_delete = '0' \n" +
                "\tAND sc.is_delete = '0'",Integer.class)>0&&
                jdbcTemplate.queryForObject("SELECT COUNT\n" +
                "\t( * ) \n" +
                "FROM\n" +
                "\t\"sys_sc_seismicsetout\" sc\n" +
                "\tLEFT JOIN sys_sc_supplies se ON sc.sc_id = se.sc_id \n" +
                "WHERE\n" +
                "\tsc.province = '"+province+"' \n" +
                "\tAND sc.city = '"+city+"' \n" +
                "\tAND sc.prefecture = '"+county+"' \n" +
                "\tAND se.is_delete = '0' \n" +
                "\tAND sc.is_delete = '0'",Integer.class)>0){
            return 1;
        }else {
            return 0;
        }
      //  return jdbcTemplate.queryForObject("SELECT count(*) from sys_sc_seismicsetout where prefecture = '" + county + "' and city = '" + city + "' and province = '" + province + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_sc_seismicsetout where  city = '" + city + "' and province = '" + province + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_sc_seismicsetout where province = '" + name + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_sc_seismicsetout where province = ? and (TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and is_delete ='0'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }
}
